package edu.cmu.cs.webapp.ioms.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import edu.cmu.cs.webapp.ioms.dao.DataAccessException;
import edu.cmu.cs.webapp.ioms.dao.LineItemDAO;
import edu.cmu.cs.webapp.ioms.dao.OrderDAO;
import edu.cmu.cs.webapp.ioms.models.Client;
import edu.cmu.cs.webapp.ioms.models.IOMSPackage;
import edu.cmu.cs.webapp.ioms.models.LineItem;
import edu.cmu.cs.webapp.ioms.models.OrderStatus;
import edu.cmu.cs.webapp.ioms.models.PurchaseOrder;
import edu.cmu.cs.webapp.ioms.utils.DateRange;
import edu.cmu.cs.webapp.ioms.utils.IOMSConstants;

public class OrderDAOImpl extends DAO implements OrderDAO {

	public OrderDAOImpl() {
		// TODO Auto-generated constructor stub

	}

	public List<PurchaseOrder> getAllOrder() throws DataAccessException {
		Connection con = null;
		PurchaseOrder po = null;
		List<PurchaseOrder> polst = new ArrayList<PurchaseOrder>();
		try {
			con = getConnection();
			Statement stmt = con.createStatement();
			String sql = "select * from po join client using(client_id)";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				po = IOMSPackage.eINSTANCE.getIOMSFactory()
						.createPurchaseOrder();
				Client c = IOMSPackage.eINSTANCE.getIOMSFactory()
						.createClient();

				po.setOrder_id(rs.getInt("order_id"));

				c.setClient_id(rs.getInt("client_id"));
				c.setLname(rs.getString("lname"));
				c.setFname(rs.getString("fname"));
				c.setEmail(rs.getString("email"));
				c.setPhone(rs.getString("phone"));
				c.setCompany(rs.getString("company"));
				c.setAddress(rs.getString("address"));
				c.setCity(rs.getString("city"));
				c.setState(rs.getString("state"));
				c.setCountry(rs.getString("country"));
				po.setClient(c);

				po.setOrder_date(rs.getDate("order_date"));
				po.setShipping_address(rs.getString("shipping_address"));
				po.setShipping_city(rs.getString("shipping_city"));
				po.setShipping_state(rs.getString("shipping_state"));
				po.setShipping_country(rs.getString("shipping_country"));
				po.setExpected_shipping_date(rs
						.getDate("expected_shipping_date"));
				po.setStatus(OrderStatus.get(rs.getString("status")));

				polst.add(po);
			}
			stmt.close();
			con.close();
		} catch (Exception e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new DataAccessException(e);
		}
		return polst;
	}

	public int addOrder(PurchaseOrder po) throws DataAccessException {
		Connection con = null;
		int result = -1;
		try {
			con = getConnection();

			PreparedStatement pstmt = con
					.prepareStatement("insert into po(client_id, order_date, shipping_address, shipping_city,"
							+ "shipping_state, shipping_country, expected_shipping_date, status) "
							+ "values (? ,?, ?, ?, ?, ?, ?, ?)");

			pstmt.setInt(1, po.getClient().getClient_id());
			pstmt.setDate(2, (Date) po.getOrder_date());
			pstmt.setString(3, po.getShipping_address());
			pstmt.setString(4, po.getShipping_city());
			pstmt.setString(5, po.getShipping_state());
			pstmt.setString(6, po.getShipping_country());
			pstmt.setDate(7, (Date) po.getExpected_shipping_date());
			pstmt.setString(8, po.getStatus().getLiteral());

			pstmt.executeUpdate();
			System.out.println("insert: " + po.toString());

			Statement stmt = con.createStatement();
			String sql = "select * from po where order_id=(select max(order_id) from po)";
			ResultSet rs = stmt.executeQuery(sql);

			if (rs.next())
				result = rs.getInt("order_id");

			stmt.close();

			pstmt.close();
			con.close();
			
			log("Create order [" + po.toString() + "]", "Add");

		} catch (Exception e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new DataAccessException(e);
		}
		return result;
	}

	@Override
	public boolean updateOrder(PurchaseOrder po) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();

			String str = "update po set ";
			if (po.getClient() != null)
				str += "client_id=" + po.getClient().getClient_id() + ",";
			if (po.getOrder_date() != null)
				str += "order_date='"
						+ (new Date(po.getOrder_date().getTime())).toString()
						+ "',";
			if (po.getShipping_address() != null)
				str += "shipping_address='" + po.getShipping_address() + "',";
			if (po.getShipping_city() != null)
				str += "shipping_city='" + po.getShipping_city() + "',";
			if (po.getShipping_state() != null)
				str += "shipping_state='" + po.getShipping_state() + "',";
			if (po.getShipping_country() != null)
				str += "shipping_country='" + po.getShipping_country() + "',";
			if (po.getExpected_shipping_date() != null)
				str += "expected_shipping_date='"
						+ (new Date(po.getExpected_shipping_date().getTime()))
								.toString() + "',";
			if (po.getStatus() != null)
				str += "status='" + po.getStatus().getLiteral() + "',";
			str = str.substring(0, str.lastIndexOf(","));
			str += " where order_id=" + po.getOrder_id();
			System.out.println(str);
			PreparedStatement pstmt = con.prepareStatement(str);
			pstmt.executeUpdate();
			System.out.println("update: " + po.toString());

			pstmt.close();
			con.close();
			
			log("Update order [" + po.toString() + "]", "Update");

		} catch (Exception e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new DataAccessException(e);
		}
		return true;

	}

	@Override
	public boolean deleteOrder(PurchaseOrder po) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();
			PreparedStatement pstmt = con
					.prepareStatement("delete from po where order_id ="
							+ po.getOrder_id());
			pstmt.executeUpdate();
			System.out.println("delete: " + po.toString());

			pstmt.close();
			con.close();
			
			log("Delete order [" + po.toString() + "]", "Delete");

		} catch (Exception e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new DataAccessException(e);
		}
		return true;
	}

	@Override
	public List<PurchaseOrder> searchOrders(Map<String, Object> criteria)
			throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		PurchaseOrder po = null;
		List<PurchaseOrder> polst = new ArrayList<PurchaseOrder>();
		try {
			con = getConnection();
			Statement stmt = con.createStatement();

			String str = "select distinct order_id, client_id, lname, fname, email, phone, company, address, city, state, country, order_date, shipping_address, shipping_city, shipping_state, shipping_country, expected_shipping_date, status "
					+ "from (((((po join client using(client_id)) join item using(order_id)) join product using(prod_id)) join formula using(prod_id)) join ingredient using(ing_id)) where ";

			if (criteria.containsKey(IOMSConstants.PURCHASE_ORDER__STATUS))
				str += "status='"
						+ criteria.get(IOMSConstants.PURCHASE_ORDER__STATUS)
						+ "' and ";

			if (criteria.containsKey(IOMSConstants.PURCHASE_ORDER__ORDER_DATE)) {
				DateRange dr = (DateRange) criteria
						.get(IOMSConstants.PURCHASE_ORDER__ORDER_DATE);

				str += "order_date between DATE('"
						+ dr.getFromDate().toString() + "') and DATE('"
						+ dr.getToDate().toString() + "') and ";
			}

			if (criteria
					.containsKey(IOMSConstants.PURCHASE_ORDER__EXPECTED_SHIPPING_DATE)) {
				DateRange dr = (DateRange) criteria
						.get(IOMSConstants.PURCHASE_ORDER__EXPECTED_SHIPPING_DATE);

				str += "expected_shipping_date between DATE('"
						+ dr.getFromDate().toString() + "') and DATE('"
						+ dr.getToDate().toString() + "') and ";
			}

			if (criteria.containsKey(IOMSConstants.CLIENT_CLIENT_ID))
				str += "client_id="
						+ ((Integer) criteria
								.get(IOMSConstants.CLIENT_CLIENT_ID))
								.intValue() + " and ";

			if (criteria.containsKey(IOMSConstants.CLIENT__LNAME))
				str += "lname='" + criteria.get(IOMSConstants.CLIENT__LNAME)
						+ "' and ";

			if (criteria.containsKey(IOMSConstants.CLIENT__FNAME))
				str += "fname='" + criteria.get(IOMSConstants.CLIENT__FNAME)
						+ "' and ";

			if (criteria.containsKey(IOMSConstants.CLIENT__EMAIL))
				str += "email='" + criteria.get(IOMSConstants.CLIENT__EMAIL)
						+ "' and ";

			if (criteria.containsKey(IOMSConstants.ING_NAME))
				str += "ingredient.name='"
						+ criteria.get(IOMSConstants.ING_NAME) + "' and ";

			if (criteria.containsKey(IOMSConstants.PROD_NAME))
				str += "prod_name='" + criteria.get(IOMSConstants.PROD_NAME)
						+ "' and ";

			str = str.substring(0, str.lastIndexOf(" and"));
			System.out.println(str);

			ResultSet rs = stmt.executeQuery(str);

			while (rs.next()) {
				po = IOMSPackage.eINSTANCE.getIOMSFactory()
						.createPurchaseOrder();
				Client c = IOMSPackage.eINSTANCE.getIOMSFactory()
						.createClient();

				po.setOrder_id(rs.getInt("order_id"));

				c.setClient_id(rs.getInt("client_id"));
				c.setLname(rs.getString("lname"));
				c.setFname(rs.getString("fname"));
				c.setEmail(rs.getString("email"));
				c.setPhone(rs.getString("phone"));
				c.setCompany(rs.getString("company"));
				c.setAddress(rs.getString("address"));
				c.setCity(rs.getString("city"));
				c.setState(rs.getString("state"));
				c.setCountry(rs.getString("country"));
				po.setClient(c);

				po.setOrder_date(rs.getDate("order_date"));
				po.setShipping_address(rs.getString("shipping_address"));
				po.setShipping_city(rs.getString("shipping_city"));
				po.setShipping_state(rs.getString("shipping_state"));
				po.setShipping_country(rs.getString("shipping_country"));
				po.setExpected_shipping_date(rs
						.getDate("expected_shipping_date"));
				po.setStatus(OrderStatus.get(rs.getString("status")));

				polst.add(po);
			}
			stmt.close();
			con.close();
		} catch (Exception e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new DataAccessException(e);
		}
		return polst;
	}

	@Override
	public int addOrderItems(PurchaseOrder po) throws DataAccessException {
		// TODO Auto-generated method stub
		int result = -1;
		PurchaseOrder order = null;

		result = addOrder(po);
		LineItemDAO dao = new LineItemDAOImpl();
		for (int i = 0; i < po.getItems().size(); ++i) {
			LineItem item = po.getItems().get(i);
			order = IOMSPackage.eINSTANCE.getIOMSFactory()
					.createPurchaseOrder();
			order.setOrder_id(result);

			item.setOrder(order);
			dao.addItem(item);
		}

		return result;
	}

}
